﻿using SqlSugar;
using SqlSugar.Extensions;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Zhao.Common.EnumEntity;
using Zhao.Common.Helper;
using Zhao.IServer;
using Zhao.IServer.Field;
using Zhao.Models.AppletDto;
using Zhao.Models.DTO;
using Zhao.Models.Entity;
using Zhao.Repository.Base;
using Zhao.Repository.UnitOfWork;
using static Dm.parser.LVal;

namespace Zhao.Service.Field
{
    /// <summary>
    /// 预约表（核心业务表）
    ///</summary>
    public class AppointmentsServer : BaseService<Appointments>, IAppointmentsServer
    {
        private readonly IUnitOfWork _unitOfWork;
        public AppointmentsServer(IBaseRepository<Appointments> baseDal, IUnitOfWork unitOfWork) : base(baseDal, unitOfWork)
        {
            _unitOfWork = unitOfWork;
        }

        /// <summary>
        /// 预约场地(后台管理预约)
        /// </summary>
        /// <returns></returns>
        public async Task<ApiResult> BookingAVenue(Guid guid, ReservationDto reservation)
        {
            var result = new ApiResult { Message = "预约失败", Success = false };
            //避免N + 1查询问题
            // 不要在每个循环中单独查询数据库验证场地是否存在，改为一次查询所有场地ID。
            //添加去重操作 避免对数据库的重复查询
            var fieldIds = reservation.infos.Select(f => f.fId).Distinct().ToList();//拿到所有的场地预约id

            var validFields = await _Client.Queryable<Fields>()
                .Where(x => fieldIds.Contains(x.FieldID) && x.VenueID.Equals(guid)).Select(f => f.FieldID).ToListAsync();

            //对比是否跟预约场地的信息一样
            if (fieldIds.Count != validFields.Count)
            {
                //Except 筛选出"第一个集合有，而第二个集合没有"的数据
                var invalidIds = string.Join(",", fieldIds.Except(validFields));
                result.Message = $"无效场地ID: {invalidIds}";
                return result;
            }

            // 步骤 2: 验证时间冲突（批量操作）
            var timeConflictQuery = reservation.infos.Select(info => new
            {
                info.fId,
                info.StartTime,
                info.EndTime
            }).ToList();

            // 3. 批量获取当天可能冲突的预约
            var validStatuses = new List<short> { 0, 3 };
            var existingReservations = await _Client.Queryable<Appointments>()
                .Where(r =>
                //获取当天
                    r.DisplayDate.Equals(reservation.displayDate) &&
                    //获取状态为已预约和待支付的
                    validStatuses.Contains(r.Status) &&
                    //只获取当前
                    fieldIds.Contains(r.FieldID) &&
                    //只获取当前管理员管理的球馆
                    r.VenueID.Equals(guid))
                //获取当前场地
                .Select(r => new AppointmentWithFields
                {
                    AppointmentID = r.AppointmentID,
                    FieldID = r.FieldID,
                    StartTime = r.StartTime,
                    EndTime = r.EndTime
                }).ToListAsync();

            // 4. 内存检查时间冲突
            var conflicts = new List<string>();

            foreach (var info in reservation.infos)
            {
                var conflict = existingReservations
                    //SelectMany将每个元素的子集合展开，合并成一个总集合
                    .FirstOrDefault(f =>
                        f.FieldID == info.fId &&
                        //忆时间区间重叠
                        // 判断两个时间段是否重叠那么其中 一个时间段的开始时间必定大于时间
                        f.StartTime < info.EndTime &&  // 条件1：现有预约开始时间 <= 新预约结束时间
                        f.EndTime > info.StartTime  // 条件2：现有预约结束时间 >= 新预约开始时间
                    );

                if (conflict != null)
                {
                    conflicts.Add(
                        $"场地 {info.fId} 在 {conflict.StartTime:HH:mm}~{conflict.EndTime:HH:mm} 已被占用"
                    );
                }
            }

            if (conflicts.Count > 0)
            {
                result.Message = "时间冲突: " + string.Join("; ", conflicts);
                result.StatusCode = 409;//请求与服务器当前状态冲突，无法完成。
                return result;
            }

            //5开启事务进行数据存储
            //先将要添加的对象创建出来
            var addAppointments = reservation.infos
                .Select(info => new Appointments
                {
                    FieldID = info.fId,
                    StartTime = info.StartTime,
                    EndTime = info.EndTime,
                    CreatedAt = DateTime.Now,
                    Status = reservation.status, // 后续待更新
                    Name = reservation.userName,
                    Mobile = reservation.phone,
                    Price = info.Price,
                    DisplayDate = reservation.displayDate,
                    VenueID = guid,
                    AppointmentType = (short)AppointmentTypeEnum.Backstage,
                }).ToList(); // 直接生成集合

            try
            {
                //启动事务
                await _Client.Ado.BeginTranAsync();
                //开始批量新增
                await _Client.Insertable(addAppointments).ExecuteCommandAsync();
                //提交事务
                await _Client.Ado.CommitTranAsync();

                result.Message = "预约成功";
                result.Success = true;
                return result;
            }
            catch (Exception ex)
            {
                //如失败回滚事务
                await _Client.Ado.RollbackTranAsync();
                result.Message = "预约失败";
                return result;
            }
        }


        /// <summary>
        /// 根据id获取一条预约数据
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public async Task<ApiResult<AppointmentsDto>> GetAppointmentById(int id)
        {
            var info = await _Client.Queryable<Appointments>()
                 .LeftJoin<Fields>((a, b) => a.FieldID == b.FieldID)
                 .Where((a, b) => a.AppointmentID == id)
                 .Select((a, b) => new AppointmentsDto
                 {
                     id = a.AppointmentID,
                     name = a.Name,
                     fName = b.FieldName,
                     sTime = a.StartTime,
                     eTime = a.EndTime,
                     phone = a.Mobile,
                     displayDate = a.DisplayDate,
                     cTime = a.CreatedAt,
                     price = a.Price,
                     status = a.Status
                 }).FirstAsync();

            return new ApiResult<AppointmentsDto>
            {
                Message = "查询成功",
                Success = true,
                Data = info
            };
        }

        /// <summary>
        /// 修改预约订单状态
        /// </summary>
        /// <param name="id">预约ID</param>
        /// <param name="status">新的状态值</param>
        /// <returns></returns>
        public async Task<ApiResult> UpdateAppointmentStatus(int id, short status)
        {
            // 使用Updateable进行数据更新
            var affectedRows = await _Client.Updateable<Appointments>()
                .SetColumns(a => new Appointments()
                {
                    Status = status,
                    // 如果需要更新时间戳可以取消注释
                    UpdatedAt = DateTime.Now
                })
                .Where(a => a.AppointmentID == id)
                .ExecuteCommandAsync();

            if (affectedRows > 0)
            {
                return new ApiResult
                {
                    Success = true,
                    Message = "订单状态更新成功"
                };
            }

            return new ApiResult
            {
                Success = false,
                Message = "更新失败，未找到对应订单"
            };

        }


        /// <summary>
        /// 取消预约订单
        /// </summary>
        /// <param name="id">预约ID</param>
        /// <returns></returns>
        public async Task<ApiResult> CancelAppointment(int id)
        {

            var resutl = new ApiResult { Success = false, Message = "取消操作未生效" };

            // 1. 验证订单有效性
            var appointment = await _Client.Queryable<Appointments>()
                .Where(a => a.AppointmentID == id)
                .FirstAsync();

            if (appointment == null)
            {
                return new ApiResult
                {
                    Success = false,
                    Message = "订单不存在"
                };
            }

            // 2. 状态验证（示例允许取消的状态：1-待支付，2-已确认）
            var cancellableStatus = new short[] { 0, 3, 7 };
            if (!cancellableStatus.Contains(appointment.Status))
            {
                return new ApiResult
                {
                    Success = false,
                    Message = $"当前状态[{appointment.Status}]不可取消"
                };
            }

            // 3. 执行取消操作
            var affectedRows = await _Client.Updateable<Appointments>()
                .SetColumns(a => new Appointments()
                {
                    Status = (short)AppointmentStatus.Canceled,
                    UpdatedAt = DateTime.Now
                })
                .Where(a => a.AppointmentID == id)
                .ExecuteCommandAsync();
            if (affectedRows > 0)
            {
                resutl.Success = true;
                resutl.Message = "取消订单成功";
            }

            return resutl;

        }


        /// <summary>
        /// 根据条件查询返回订单信息
        /// </summary>
        /// <param name="expressionable"></param>
        /// <param name="pageIndex"></param>
        /// <param name=""></param>
        /// <param name="pageSize"></param>
        /// <returns></returns>
        public async Task<PageModel<AppointmentsDto>> GetInfos(Expressionable<Appointments, Fields> expressionable, int pageIndex, int pageSize)
        {
            //获取总条数
            RefAsync<int> totalCount = 0;

            var data = await _Client.Queryable<Appointments>()
                .LeftJoin<Fields>((a, b) => a.FieldID == b.FieldID)
                .WhereIF(expressionable != null, expressionable.ToExpression())
                .Select((a, b) => new AppointmentsDto
                {
                    id = a.AppointmentID,
                    name = a.Name,
                    fName = b.FieldName,
                    sTime = a.StartTime,
                    eTime = a.EndTime,
                    phone = a.Mobile,
                    displayDate = a.DisplayDate,
                    cTime = a.CreatedAt,
                    price = a.Price,
                    status = a.Status,
                    typeId=a.AppointmentType,
                })
                .ToPageListAsync(pageIndex, pageSize, totalCount).ConfigureAwait(false);

            int totalPage = Math.Ceiling(totalCount.ObjToDecimal() / pageSize.ObjToDecimal()).ObjToInt();

            return new PageModel<AppointmentsDto>() { count = totalCount, PageCount = totalPage, PageIndex = pageIndex, PageSize = pageSize, data = data, code = totalCount == 0 ? -1 : 0, msg = totalCount == 0 ? "未查询到任何数据" : "" };
        }

        #region 小程序端api

        public async Task<ApiResult> AppleBookingAVenue(AppleReservation reservation)
        {
            var result = new ApiResult { Message = "预约失败", Success = false };
            //避免N + 1查询问题
            // 不要在每个循环中单独查询数据库验证场地是否存在，改为一次查询所有场地ID。
            //添加去重操作 避免对数据库的重复查询
            var fieldIds = reservation.infos.Select(f => f.fId).Distinct().ToList();//拿到所有的场地预约id

            var validFields = await _Client.Queryable<Fields>()
                .Where(x => fieldIds.Contains(x.FieldID) && x.VenueID.Equals(reservation.vuenId)).Select(f => f.FieldID).ToListAsync();

            //对比是否跟预约场地的信息一样
            if (fieldIds.Count != validFields.Count)
            {
                //Except 筛选出"第一个集合有，而第二个集合没有"的数据
                var invalidIds = string.Join(",", fieldIds.Except(validFields));
                result.Message = $"无效场地ID: {invalidIds}";
                return result;
            }

            // 步骤 2: 验证时间冲突（批量操作）
            var timeConflictQuery = reservation.infos.Select(info => new
            {
                info.fId,
                info.StartTime,
                info.EndTime
            }).ToList();

            // 3. 批量获取当天可能冲突的预约
            var validStatuses = new List<short> { 0, 3 };
            var existingReservations = await _Client.Queryable<Appointments>()
                .Where(r =>
                //获取当天
                    r.DisplayDate.Equals(reservation.displayDate) &&
                    //获取状态为已预约和待支付的
                    validStatuses.Contains(r.Status) &&
                    //只获取当前
                    fieldIds.Contains(r.FieldID) &&
                    //只获取当前管理员管理的球馆
                    r.VenueID.Equals(reservation.vuenId))
                //获取当前场地
                .Select(r => new AppointmentWithFields
                {
                    AppointmentID = r.AppointmentID,
                    FieldID = r.FieldID,
                    StartTime = r.StartTime,
                    EndTime = r.EndTime
                }).ToListAsync();

            // 4. 内存检查时间冲突
            var conflicts = new List<string>();

            foreach (var info in reservation.infos)
            {
                var conflict = existingReservations
                    //SelectMany将每个元素的子集合展开，合并成一个总集合
                    .FirstOrDefault(f =>
                        f.FieldID == info.fId &&
                        //忆时间区间重叠
                        // 判断两个时间段是否重叠那么其中 一个时间段的开始时间必定大于时间
                        f.StartTime < info.EndTime &&  // 条件1：现有预约开始时间 < 新预约结束时间
                        f.EndTime > info.StartTime  // 条件2：现有预约结束时间 > 新预约开始时间
                    );

                if (conflict != null)
                {
                    conflicts.Add(
                        $"场地 {info.fId} 在 {conflict.StartTime:HH:mm}~{conflict.EndTime:HH:mm} 已被占用"
                    );
                }
            }

            if (conflicts.Count > 0)
            {
                result.Message = "时间冲突: " + string.Join("; ", conflicts);
                result.StatusCode = 409;//请求与服务器当前状态冲突，无法完成。
                return result;
            }

            try
            {
                //启动事务
                await _Client.Ado.BeginTranAsync();

                //开始创建用户订单
                var order = new Orders
                {
                    UserId = reservation.userId,
                    TotalAmount = reservation.totalPrice,
                    PaymentMethod = reservation.PaymentType,
                    TransactionID = reservation.Transaction,
                    CreatedAt = DateTime.Now,
                    PaymentTime= DateTime.Now,
                    TypeId = 7,
                    Status = 3,
                };

                var orderId= await _Client.Insertable(order).ExecuteReturnIdentityAsync();

                //5开启事务进行数据存储
                //先将要添加的对象创建出来
                var addAppointments = reservation.infos
                    .Select(info => new Appointments
                    {
                        FieldID = info.fId,
                        StartTime = info.StartTime,
                        EndTime = info.EndTime,
                        CreatedAt = DateTime.Now,
                        Status = reservation.status, // 后续待更新
                        Name = reservation.userName,
                        Mobile = reservation.phone,
                        Price = info.Price,
                        DisplayDate = reservation.displayDate,
                        VenueID = reservation.vuenId,
                        AppointmentType = (short)AppointmentTypeEnum.Apple,
                        OrderId = orderId,
                    }).ToList(); // 直接生成集合

                //开始批量新增
                await _Client.Insertable(addAppointments).ExecuteCommandAsync();

                if (reservation.PaymentType == 1)
                {
                    //去修改用户的余额
                    var user = await _Client.Queryable<Users>()
                        .Where(x => x.UserID == reservation.userId).FirstAsync();

                    user.AccountBalance -= reservation.totalPrice;

                    await _Client.Updateable(user).ExecuteCommandAsync();
                }

                //提交事务
                await _Client.Ado.CommitTranAsync();

                result.Message = "预约成功";
                result.Success = true;
                return result;
            }
            catch (Exception ex)
            {
                //如失败回滚事务
                await _Client.Ado.RollbackTranAsync();
                result.Message = "预约失败";
                return result;
            }

        }

        /// <summary>
        /// 获取用户的预约订单信息
        /// </summary>
        /// <param name="userId">用户ID</param>
        /// <param name="status">订单状态：0-待支付，3-已预约，5-已完成，7-已取消</param>
        /// <param name="pageIndex">页码</param>
        /// <param name="pageSize">每页大小</param>
        /// <returns></returns>
        public async Task<PageModel<AppointmentOrderDto>> GetUserAppointmentOrders(int userId, short? status = -1, int pageIndex = 1, int pageSize = 10)
        {
            // 验证状态值是否有效
            if (status.HasValue && status.Value != -1 && !new[] { 0, 3, 5, 7 }.Contains(status.Value))
            {
                return new PageModel<AppointmentOrderDto>
                {
                    code = -1,
                    msg = "无效的订单状态值",
                    data = new List<AppointmentOrderDto>()
                };
            }

            // 获取总条数（按订单统计）
            RefAsync<int> totalCount = 0;

            // 先查询符合条件的订单ID
            var orderQuery = _Client.Queryable<Orders>()
                .LeftJoin<Appointments>((o, a) => o.OrderID == a.OrderId)
                .Where((o, a) => 
                    o.UserId == userId && 
                    o.TypeId == 7 && // 场地预定类型
                    a.AppointmentType == 1); // 小程序预约类型

            //筛选那些指定状态的订单(有一些订单会有不同的预约状态)
            //一个订单最多可以有四条预约信息，每条预约信息都不同
            if (status.HasValue && status.Value != -1)
            {
                orderQuery = orderQuery.Where((o, a) => a.Status == status.Value);
            }

            // 获取符合条件的订单ID列表
            var orderIds = await orderQuery
                .Select((o, a) => o.OrderID)
                .Distinct()
                .ToListAsync();

            // 获取总订单数
            totalCount = orderIds.Count;

            // 分页获取订单ID
            var pagedOrderIds = orderIds
                .Skip((pageIndex - 1) * pageSize)
                .Take(pageSize)
                .ToList();

            if (!pagedOrderIds.Any())
            {
                return new PageModel<AppointmentOrderDto>
                {
                    count = 0,
                    PageCount = 0,
                    PageIndex = pageIndex,
                    PageSize = pageSize,
                    data = new List<AppointmentOrderDto>(),
                    code = -1,
                    msg = "未查询到任何数据"
                };
            }

            // 查询订单基本信息 - 先获取订单信息避免重复
            var orders = await _Client.Queryable<Orders>()
                .LeftJoin<Users>((o, u) => o.UserId == u.UserID)
                .Where((o, u) => pagedOrderIds.Contains(o.OrderID))
                .Select((o, u) => new
                {
                    OrderId = o.OrderID,
                    TotalAmount = o.TotalAmount,
                    PaymentMethod = o.PaymentMethod,
                    PaymentTime = o.PaymentTime,
                    CreatedAt = o.CreatedAt,
                    OrderStatus = o.Status,
                    UserName = u.RealName,
                    Phone = u.Phone,
                })
                .OrderByDescending(o => o.CreatedAt)
                .ToListAsync();
                
            // 获取每个订单的场馆信息(取第一个预约的场馆信息)
            var venueInfos = await _Client.Queryable<Appointments>()
                .LeftJoin<Venues>((a, v) => a.VenueID == v.VenueID)
                .Where(a => pagedOrderIds.Contains(a.OrderId.Value))
                .Select((a, v) => new
                {
                    OrderId = a.OrderId,
                    DisplayDate = a.DisplayDate,
                    VenueId = v.VenueID,
                    VenueName = v.VenueName,
                    VenueAddress = v.Address,
                    VenuePhone = v.Phone,
                    VenueImage = v.Images
                })
                .Distinct()
                .ToListAsync();

            // 查询每个订单的场地预约信息
            var fieldAppointments = await _Client.Queryable<Appointments>()
                .LeftJoin<Fields>((a, f) => a.FieldID == f.FieldID)
                .Where((a, f) => pagedOrderIds.Contains(a.OrderId.Value))
                .WhereIF( status!=-1,(a,f)=>a.Status==status) //筛选Appointments中的订单状态
                .Select((a, f) => new
                {
                    OrderId = a.OrderId,
                    AppointmentId = a.AppointmentID,
                    FieldName = f.FieldName,
                    StartTime = a.StartTime,
                    EndTime = a.EndTime,
                    Status = a.Status,
                    Price = a.Price
                })
                .ToListAsync();

            // 组装返回数据
            var result = orders.Select(o => 
            {
                // 获取对应的场馆信息
                var venueInfo = venueInfos.FirstOrDefault(v => v.OrderId == o.OrderId);
                
                return new AppointmentOrderDto
                {
                    OrderId = o.OrderId,
                    TotalAmount = o.TotalAmount,
                    PaymentMethod = o.PaymentMethod,
                    PaymentTime = o.PaymentTime,
                    CreatedAt = o.CreatedAt,
                    OrderStatus = o.OrderStatus,
                    DisplayDate = venueInfo?.DisplayDate,
                    UserName = o.UserName,
                    Phone = o.Phone,
                    VenueId = venueInfo?.VenueId ?? Guid.Empty,
                    VenueName = venueInfo?.VenueName,
                    VenueAddress = venueInfo?.VenueAddress,
                    VenuePhone = venueInfo?.VenuePhone,
                    VenueImage = venueInfo?.VenueImage,
                    Info = fieldAppointments
                        .Where(f => f.OrderId == o.OrderId)
                        .Select(f => new FieldAppointmentInfo
                        {
                            AppointmentId = f.AppointmentId,
                            FieldName = f.FieldName,
                            Stime = f.StartTime.ToString("HH:mm"),
                            Etime = f.EndTime.ToString("HH:mm"),
                            Status = f.Status,
                            Price = f.Price
                        })
                        .ToList()
                };
            }).ToList();

            int totalPage = Math.Ceiling(totalCount.ObjToDecimal() / pageSize.ObjToDecimal()).ObjToInt();

            return new PageModel<AppointmentOrderDto>
            {
                count = totalCount,
                PageCount = totalPage,
                PageIndex = pageIndex,
                PageSize = pageSize,
                data = result,
                code = totalCount == 0 ? -1 : 0,
                msg = totalCount == 0 ? "未查询到任何数据" : ""
            };
        }

        /// <summary>
        /// 取消用户的预约订单
        /// </summary>
        /// <param name="userId">用户ID</param>
        /// <param name="orderId">要取消的订单ID</param>
        /// <returns>操作结果</returns>
        public async Task<ApiResult> CancelUserAppointments(int userId, int orderId)
        {
            var result = new ApiResult { Success = false, Message = "取消预约失败" };

            try
            {
                // 1. 查询订单信息，验证订单是否属于当前用户
                var order = await _Client.Queryable<Orders>()
                    .Where(o => o.OrderID == orderId && o.UserId == userId)
                    .FirstAsync();

                if (order == null)
                {
                    result.Message = "未找到指定的订单或您没有权限取消该订单";
                    return result;
                }

                // 2. 查询该订单下的所有预约
                var appointments = await _Client.Queryable<Appointments>()
                    .Where(a => a.OrderId == orderId)
                    .ToListAsync();

                if (!appointments.Any())
                {
                    result.Message = "该订单下没有预约记录";
                    return result;
                }

                // 3. 验证订单是否可以取消
                var now = DateTime.Now;
                
                // 检查订单状态
                if (order.Status != 0 && order.Status != 3)
                {
                    result.Message = $"订单状态为 {order.Status}，无法取消";
                    return result;
                }
                
                // 检查是否有预约已经开始
                foreach (var appointment in appointments)
                {
                    if (appointment.Status != 0 && appointment.Status != 3)
                    {
                        result.Message = "订单中包含已完成或已取消的预约，无法取消";
                        return result;
                    }
                    
                    // 验证预约时间是否已经开始
                    if (TimeOnly.TryParse(appointment.StartTime.ToString("HH:mm"), out TimeOnly timeOnly))
                    {
                        DateTime appointmentDateTime = appointment.DisplayDate.GetCombinedDateTime(timeOnly);
                        
                        // 如果预约已经开始，则不允许取消整个订单
                        if (appointmentDateTime <= now)
                        {
                            result.Message = $"预约 #{appointment.AppointmentID} 已经开始，无法取消订单";
                            return result;
                        }
                    }
                }

                // 4. 开始事务处理
                await _Client.Ado.BeginTranAsync();

                try
                {
                    // 计算需要退款的总金额
                    decimal totalRefundAmount = appointments.Sum(a => a.Price);
                    
                    // 获取用户信息
                    var user = await _Client.Queryable<Users>()
                        .Where(u => u.UserID == userId)
                        .FirstAsync();
                    
                    if (user == null)
                    {
                        await _Client.Ado.RollbackTranAsync();
                        result.Message = "用户信息不存在";
                        return result;
                    }

                    // 5. 更新所有预约状态为已取消(7)
                    foreach (var appointment in appointments)
                    {
                        appointment.Status = 7; // 已取消
                        appointment.UpdatedAt = DateTime.Now;
                    }
                    await _Client.Updateable(appointments).ExecuteCommandAsync();

                    // 6. 创建退款订单记录
                    var refundOrder = new Orders
                    {
                        UserId = userId,
                        TotalAmount = totalRefundAmount,
                        CreatedAt = DateTime.Now,
                        PaymentTime = DateTime.Now,
                        TypeId = 9, // 订单取消退款
                        Status = 3, // 已完成
                        RefundTransaction = $"订单 #{orderId} 取消退款"
                    };
                    
                    // 插入退款订单并获取ID
                    var refundOrderId = await _Client.Insertable(refundOrder).ExecuteReturnIdentityAsync();
                    
                    // 7. 更新原订单状态为已退款(5)，并添加退款信息
                    await _Client.Updateable<Orders>()
                        .SetColumns(o => new Orders
                        {
                            Status = 5, // 已退款
                            RefundAmount = totalRefundAmount,
                            RefundTransactionId = refundOrderId,
                            RefundTransaction = $"已退款 {totalRefundAmount:F2} 元，退款单号: {refundOrderId}"
                        })
                        .Where(o => o.OrderID == orderId)
                        .ExecuteCommandAsync();

                    // 8. 如果是余额支付，则退款到用户余额
                    if (order.PaymentMethod == 1) // 余额支付
                    {
                        // 更新用户余额
                        user.AccountBalance += totalRefundAmount;
                        await _Client.Updateable(user).ExecuteCommandAsync();
                    }
                    

                    // 提交事务
                    await _Client.Ado.CommitTranAsync();

                    result.Success = true;
                    result.Message = "订单取消成功";
                    return result;
                }
                catch (Exception ex)
                {
                    // 回滚事务
                    await _Client.Ado.RollbackTranAsync();
                    result.Message = $"取消订单失败: {ex.Message}";
                    return result;
                }
            }
            catch (Exception ex)
            {
                result.Message = $"处理请求时发生错误: {ex.Message}";
                return result;
            }
        }


        #endregion

        /// <summary>
        /// 更新历史场地预约状态
        /// </summary>
        /// <returns>更新的记录数</returns>
        public async Task<int> UpdateHistoricalAppointmentsStatusAsync()
        {
            int updateCount = 0;

            // 获取当前日期
            var currentDate = DateTime.Today.ToString("yyyy-MM-dd");

            // 开启事务
            await _Client.Ado.UseTranAsync(async () =>
            {
                try
                {
                    // 查询所有过去日期的预约，排除已取消和已完成的预约
                    var appointments = await _Client.Queryable<Appointments>()
                        .Where(x => x.Status != 5 && // 排除已取消
                               x.Status != 7 && // 排除已完成
                               SqlFunc.CompareTo(x.DisplayDate, currentDate) < 0)
                        .ToListAsync();

                    // 遍历预约，更新状态
                    foreach (var appointment in appointments)
                    {
                        // 如果预约日期已过，则更新为已完成
                        appointment.Status = 7; // 7=已完成
                        appointment.UpdatedAt = DateTime.Now;
                    }

                    // 更新修改过的预约
                    if (appointments.Any())
                    {
                        updateCount = await _Client.Updateable(appointments)
                            .UpdateColumns(x => new { x.Status, x.UpdatedAt })
                            .ExecuteCommandAsync();

                        // 记录日志
                        // 这里假设没有直接访问日志服务，可以根据实际情况调整
                        Console.WriteLine($"历史场地预约状态已更新：{updateCount}条记录状态已更新为已完成");
                    }
                }
                catch (Exception ex)
                {
                    Console.WriteLine($"更新历史场地预约状态失败：{ex.Message}");
                    throw; // 抛出异常会自动回滚事务
                }
            });

            return updateCount;
        }

    }
}

